var express = require('express');
var router = express.Router();
var fs = require('fs');
var path = require('path');
// 导入MySQL模块
var mysql = require('mysql');
var dbConfig = require('../DB/dbConf');
var userSQL = require('../DB/usersql');
// 使用DBConfig.js的配置信息创建一个MySQL连接池
var pool = mysql.createPool(dbConfig);
//导入uuid
var uuid = require('node-uuid');
//导入os模块
var os=require('os');
// 导入excel处理模块
var xlsx=require('node-xlsx');
//导入、配置文件上传组件
var multer = require('multer');
var storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, './public/upload')
  },
  filename: function (req, file, cb) {
    cb(null,file.originalname);
  }
});
var upload = multer({ storage: storage });
///////////////////////用户模块///////////////////////////////////

//获取用户信息
router.get('/getUserInfo', function (req, res, next) {
  pool.getConnection((err, connection) => {
    connection.query(userSQL.getUserById, req.query.uid, (err, resutls) => {
      if (err) throw err;
      if (resutls.length > 0) res.end(JSON.stringify(resutls));
      else res.end('null');
    });
    connection.release();
  });
});

///////////////////分类模块///////////////////////
//增加分类
router.get('/addcate', function (req, res, next) {
  pool.getConnection((err, connection) => {
    var parms = [req.query.name, imagename.length!=0?imagename[0]:'', req.query.note, req.query.belong];
    connection.query('insert into cate(name,pic,note,belong) values(?,?,?,?)', parms, (err, resutls) => {
      if (err) throw err;
      if (resutls.affectedRows == 1) res.json(1);
      else res.json(0);
    });
    connection.release();
  });
});
//删除分类
router.get('/delcate', function (req, res, next) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('delete from cate where id=?', req.query.id, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
//修改分类
router.get('/editcate', function (req, res, next) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    var parms = [req.query.name, req.query.pic, req.query.note, req.query.belong, req.query.id];
    connection.query('update cate set name=?,pic=?,note=?,belong=? where id=?', parms, (err, results) => {
      if (err) throw err;
      if (results.affectedRows > 0) {
        res.json(1)
      } else {
        res.json(0);
      }
    });
    connection.release();
  });
});
//查找分类byID
router.get('/findcate', function (req, res, next) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('select * from cate where id=?', req.query.id, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
    connection.release();
  });
});
//查找分类byBelong
router.get('/findParentCate', function (req, res, next) {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('select * from cate where belong=?', req.query.id, (err, results) => {
      if (err) throw err;
      res.json(results);
    });
    connection.release();
  });
});
//查找所有分类
router.get('/listcate', function (req, res, next) {
  var page = parseInt(req.query.page);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(id) AS COUNT FROM cate', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
    });
    var sql = 'SELECT c1.id,c1.`name`,c1.pic,c1.note,c2.id as parent_id,c2.`name` as parent_name FROM cate c1 LEFT JOIN cate c2 ON (c1.belong=c2.id) ORDER BY c1.id limit ?,?';
    connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": count, "data": results });
    });
    connection.release();
  });
});

//分类图片上传
var imagename = [];
router.post("/uploadImgs",upload.any(), function (req, res, next) {

  imagename.splice(0,imagename.length);       // 1.先清空数组 imagename=[]
  imagename.push(req.files[0].originalname);  // 2.将文件名压入数组
  res.json({ code: 200});
});

////////////////////////////考勤相关模块/////////////////////////


// 考勤历史记录
router.get('/listAllRecord', (req, res, next) => {
  var page = parseInt(req.query.page);
  var limit = parseInt(req.query.limit);
  pool.getConnection((err, connection) => {
    var count;
    if (err) throw err;
    connection.query('SELECT COUNT(id) AS COUNT FROM sign_in', (err, results) => {
      if (err) throw err;
      count = results[0].COUNT;
    });
    var sql = 'SELECT u.username,s.node,s.time,s.state FROM sign_in AS s LEFT JOIN `user` AS u ON(s.userid=u.uid) limit ?,?';
    connection.query(sql, [(page - 1) * limit, limit], (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": count, "data": results });
    });
    connection.release();
  });
});

// 显示课程评分列表
router.get('/listAllCourseScore', (req, res, next) => {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('select * from course_score', (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": '0', "data": results });
    });
    connection.release();
  });
});

// 手动录入数据后台显示
// localhost:3000/admin/inputdatalist
router.get('/inputdatalist', (req, res, next) => {
  pool.getConnection((err, connection) => {
    if (err) throw err;
    connection.query('SELECT * FROM inputdata', (err, results) => {
      if (err) throw err;
      res.json({ "code": 0, "msg": "", "count": '0', "data": results });
    });
    connection.release();
  });
});

router.get('/sysInfo',(req,res,next)=>{
  var sysInfo=[];
  sysInfo.push(os.arch());
  sysInfo.push(os.cpus().length);
  sysInfo.push(os.freemem());
  sysInfo.push(os.totalmem());
  sysInfo.push(process.version);
  sysInfo.push(os.type());
  sysInfo.push(os.uptime());
  sysInfo.push(__dirname);
  res.json(sysInfo);
});

/**
 * 版本对比
 */
router.get('/updateversion',(req,res,next)=>{
  //本机版本
  var oldVersion = req.query.versionCode;
  pool.getConnection(function(err,connection){
    if(err)throw err;
    connection.query('select versionCode,versionName,versionDescript,apkUrl from apkversion order by id desc limit 1',function(err,result){
        if(err) throw err;
        //本机版本与最新版本对比
        if(oldVersion<result[0].versionCode){
          //更新新版本
          res.json(result);
        }else{
          //不更新版本
          res.json(null);
        }
        
    });
    connection.release();
  });
});
/**
 * 下载新版本
 */
router.get('/downapk',(req,res,next)=>{
  //获取下载版本的路径
  var apkUrl = req.query.apkUrl;
  //拼接路径
  var rootUrl = path.resolve(__dirname,'..');
  var url = rootUrl+"/public/downloadapk/version/"+apkUrl;
  var f = fs.createReadStream(url);
  res.writeHead(200,{'Content-Type':'application/force-download','Content-Disposition':'attachment; filename='+apkUrl});
  //下载
  f.pipe(res);
});
////////////////////学生表操作///////////////////////////////////////
// 保存客户端的学生表
router.get('/saveCouresTable',(req,res,next)=>{
  var filepath=path.resolve(__dirname,'..')+'/public/upload/1.xlsx';
  var workSheetsFromFile=xlsx.parse(filepath);//解析xlsx文件
  var studata=workSheetsFromFile[1].data;//获取工作表0中数据
  studata.splice(0,2);//删除表格标题和表头
  var sql='INSERT INTO student(id,name,sex,birthday,student_id) VALUES ';
  // 为每条记录增加uuid，拼接sql语句
  for(var i=0;i<studata.length;i++){
    (studata[i][2]=='男')?studata[i][2]=1:studata[i][2]=0;//数据格式转换
    studata[i].push(uuid.v4());//增加每条记录的uuid
    sql+='(\''+studata[i][0]+'\',\''+studata[i][1]+'\',\''+studata[i][2]+'\',\''+studata[i][3]+'\',\''+studata[i][4]+'\'),';
  }
  sql=sql.slice(0,-1);//去掉最后多余的逗号
  console.log(sql)
  // 插入记录到数据库
  console.log(workSheetsFromFile);
  xlsx
  // return
  pool.getConnection((err,connection)=>{
    if(err) throw err;
      connection.query(sql,(err,results)=>{
        if(err) throw err;
        res.json({record:results.affectedRows});
    });
    connection.release();
  });
});

////////////////////课程表操作///////////////////////////////////////
// 保存客户端的课程表
router.post('/saveStudentTable',(req,res,next)=>{
  var courseTable=req.body.courseTable;
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    connection.query('sql',(err,results)=>{
      if(err) throw err;
      console.log(results);
    });
    connection.release();
  });
});
// 增加班级内容
router.get('/getClassList', function(req, res, next) {  
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    var sql = 'SELECT * FROM class';
      connection.query(sql,function(err,result){
        if(err) throw err;
          res.json(result);
      })
    connection.release(); 
    }) 
 }); 
//  解析并存入数据库
 router.post('/excelRead',upload.any(),(req,res,next)=>{
   var filesName=req.files[0].originalname;
   console.log(filesName);
  var filepath=path.resolve(__dirname,'..')+'/public/upload/'+filesName;
  var workSheetsFromFile=xlsx.parse(filepath);//解析xlsx文件
  var studata=workSheetsFromFile[0].data;//获取工作表0中数据
  studata.splice(0,1);//删除表格标题和表头
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    var sql = 'INSERT INTO student(id,name,sex,birthday,student_id,class_id) VALUES';
    for(var i=0;i<studata.length;i++){
      (studata[i][2]=='男')?studata[i][2]=1:studata[i][2]=0;//数据格式转换
      studata[i].push(uuid.v4());//增加每条记录的uuid
      sql+='(\''+studata[i][0]+'\',\''+studata[i][1]+'\',\''+studata[i][2]+'\',\''+studata[i][3]+'\',\''+studata[i][4]+'\',\''+req.body.sel+'\'),';
    }
    sql=sql.slice(0,-1);//去掉最后多余的逗号
    console.log(sql)
    connection.query(sql,function(err,rows,fileds){
      if(err){
        console.log('INSERT ERROR -',err.message)
        return
      }
      console.log('INSERT SUCESS');
    connection.release(); 
    })

 }) 
}); 
// 手动录入提交服务器存数据库；
router.get('/datarecords',(req,res,next)=>{
  console.log(req.query);
  pool.getConnection((err, connection) => {
    var parms = ["213",req.query.sets1, req.query.sets2,req.query.sets3,req.query.sets4,req.query.sets5,req.query.sets6,"333"];
    connection.query('insert into inputdata(uuid,drinks,checks,wine,works,game,sport,userid)  values(?,?,?,?,?,?,?,?)', parms, (err, resutls) => {
      if (err) throw err;
      res.json({ code: 200});
      // if (resutls.affectedRows == 1) res.json(1);
      // else res.json(0);
    });
    connection.release();
  });
    
})

// 增加数据库表内类名
router.get('/addCates',(req,res,next)=>{
  console.log(req.query);
  pool.getConnection((err, connection) => {
    var parms = [req.query.cid, req.query.cname];
    connection.query('insert into category_copy(cid,cname)  values(?,?)', parms, (err, resutls) => {
      if (err) throw err;
      res.json({ code: 200});
      // if (resutls.affectedRows == 1) res.json(1);
      // else res.json(0);
    });
    connection.release();
})
});
// 添加热搜词语和热门推荐

// 查数据库遍历列表
router.get('/getClassLists', function(req, res, next) {  
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    var sql = 'SELECT * FROM category_copy';
      connection.query(sql,function(err,result){
        if(err) throw err;
          res.json(result);
      })
    connection.release(); 
    }) 
 }); 

module.exports = router;
